﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using NPOI.HSSF.UserModel;
using IHISCETeim.BLL;
using IHISCETeim.Library;
using IHISCETeim.Model;
using IHISCETeim.DAL;
namespace IHISCETeim.FXTool
{
    public partial class FXSJZYFrm : Form
    {
        public FXSJZYFrm()
        {
            InitializeComponent();
        }
        #region 打开Execl
        HSSFWorkbook hssfworkbook;

        void InitializeWorkbook(string path)
        {
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);


            }
        }

        void ConvertToDataTable()
        {
            //经销商
            HSSFSheet sheet = hssfworkbook.GetSheet("经销商") as HSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            //for (int j = 0; j < 5; j++)
            //{
            //    dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());
            //}
            //rows.MoveNext();
            int titleRowNum = sheet.FirstRowNum;
            HSSFRow titlerow = (HSSFRow)sheet.GetRow(titleRowNum);
            //dt.Columns.Add("简称");

            for (int j = 0; j < titlerow.LastCellNum; j++)
            {
                dt.Columns.Add(titlerow.Cells[j].ToString());
            }
            rows.MoveNext();
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    HSSFCell cell = row.GetCell(i) as HSSFCell;

                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            //经销商简称
            //foreach (DataRow dr in dt.Rows)
            //{
            //    string x = SBOController.GetAliasNameByCardName(dr["代理店"].ToString());

            //    if (x == "")
            //        this.txtImportResult.Text = "导入数据中代理店全称与SAP系统全称有不一致的，请手工更新简称";

            //    dr["简称"] = x;
            //}

            //
            dt.TableName = "经销商";
            dataSet1.Tables.Add(dt);
            //客户
            HSSFSheet sheet2 = hssfworkbook.GetSheet("客户明细") as HSSFSheet;
            System.Collections.IEnumerator rows2 = sheet2.GetRowEnumerator();

            DataTable dt2 = new DataTable();
            //for (int j = 0; j < 5; j++)
            //{
            //    dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());
            //}
            //rows.MoveNext();
            int titleRowNum2 = sheet2.FirstRowNum;
            HSSFRow titlerow2 = (HSSFRow)sheet2.GetRow(titleRowNum2);
           // dt2.Columns.Add("简称");
            for (int j = 0; j < titlerow2.LastCellNum; j++)
            {
                dt2.Columns.Add(titlerow2.Cells[j].ToString());
            }
            rows2.MoveNext();
            while (rows2.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows2.Current;
                DataRow dr = dt2.NewRow();

                for (int i = 0; i < dt2.Columns.Count; i++)
                {
                    HSSFCell cell = row.GetCell(i) as HSSFCell;


                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt2.Rows.Add(dr);
            }
            //经销商简称
            //foreach (DataRow dr in dt2.Rows)
            //{
            //    dr["简称"] = SBOController.GetAliasNameByCardName(dr["代理店"].ToString());
            //}
            dt2.TableName = "客户明细";
            dataSet1.Tables.Add(dt2);


        }
        #endregion
        private void btnSelectFile_Click(object sender, EventArgs e)
        {
            string path;

            //OpenFileDialog of = new OpenFileDialog();

            // of.Filter = "Excel文件(*.xls)|*.xls";
            if (this.openFileDialog1.ShowDialog() != System.Windows.Forms.DialogResult.OK)
            {
                return;
            }

            path = openFileDialog1.FileName;

            this.txtFilePath.Text = path;
            ////

            InitializeWorkbook(path);
            ConvertToDataTable();

            gvData.DataSource = dataSet1.Tables[0];

            gvdata2.DataSource = dataSet1.Tables[1];
            // this.btnBegin.IsEnabled = true;

           // this.txtMsg.Text = string.Format("本次需要导入[{0}]条,请核对需导入的数据与Excel是否一致.", dataSet1.Tables[0].Rows.Count);

        }
        OUserInfo objNews = null;
        private LoginState IsAuthenticated(string userName, string pwd)
        {
            try
            {
                objNews = CBO.FillObject<OUserInfo>(OUserDAL.UserLogin(userName, StringHelper.GetPwdEncrypt(pwd), ""));
                // UserId = objNews.UserId;
                if (objNews == null)
                {
                    return LoginState.inputerror;
                }
                else if (objNews.State == 0)
                {
                    return LoginState.other;
                }
                else
                {
                    return LoginState.success;
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
                // UserId = 0;
                return LoginState.other;
            }
            finally
            {

            }
        }
        private void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                //InitializeWorkbook(@"xls\Book1.xls");
                //ConvertToDataTable();

                //gvData.DataSource = dataSet1.Tables[0];
                string username = this.txtUserName.Text;
                string pwd = this.txtPwd.Text;

                LoginState ls = IsAuthenticated(username, pwd);
                if (ls != LoginState.success)
                {
                    MessageBox.Show("登陆失败，请确认你的用户密码");
                    return;
                }

                this.txtImportResult.Text = "";

                FXSJZYController.Clear();

                this.txtImportResult.Text += "已清楚原所有记录" + "\r\n"; ;
                //经销商
                DataTable importdt = this.dataSet1.Tables[0];
                FXSJZYAgentInfo objAgent = null;

                foreach (DataRow dr in importdt.Rows)
                {
                    objAgent = new FXSJZYAgentInfo();
                    objAgent.AliasName = dr["代理店"].ToString();
                    //objAgent.CardName = dr["代理店"].ToString();
                    objAgent.TotalRecovery = Convert.ToDecimal(dr["回收总额"]);
                    objAgent.OverdueAmount = Convert.ToDecimal(dr["逾期总额"]);
                    objAgent.RecoveryRent = Convert.ToDecimal(dr["已回收租金"]);
                    objAgent.YuQiLv = dr["逾期率"].ToString();
                    objAgent.NotRecoveryRent = Convert.ToDecimal(dr["未回收租金"]);
                    objAgent.OverdueRatio = dr["逾期比例"].ToString();
                    objAgent.Range3 = Convert.ToDecimal(dr["距离3%"]);
                    objAgent.Range1 = Convert.ToDecimal(dr["距离1%"]);
                    objAgent.Goal = Convert.ToDecimal(dr["目标"]);
                    objAgent.CreateDate = DateTime.Now;

                    int x = FXSJZYController.FXSJZYAgentImportFromExecl(objAgent);
                    if (x<=0)
                        this.txtImportResult.Text += "导入失败" + "\r\n";
                }

                //客户明细
                DataTable importDetailDt = this.dataSet1.Tables[1];
                FXSJZYDetailInfo objDetail = new FXSJZYDetailInfo();
                foreach (DataRow dr in importDetailDt.Rows)
                {
                    objDetail = new FXSJZYDetailInfo();
                    objDetail.AliasName = dr["代理店"].ToString();
                   // objDetail.CardName = dr["代理店"].ToString();
                    objDetail.CustomerName = dr["顧客名"].ToString();
                    objDetail.ContractNo = dr["契約番号"].ToString();
                    objDetail.Balance1 = Convert.ToDecimal(dr["差額1"]);
                    objDetail.Balance2 = Convert.ToDecimal(dr["差額2"]);
                    objDetail.Balance3 = Convert.ToDecimal(dr["差額3"]);
                    objDetail.OverdueAmount = Convert.ToDecimal(dr["逾期总额"]);
                    objDetail.OverdueNumber = Convert.ToDecimal(dr["逾期次数"]);
                    int x = FXSJZYController.FXSJZYDetailImportFromExecl(objDetail);
                    if (x <= 0)
                        this.txtImportResult.Text += "导入失败" + "\r\n";
                }


                MessageBox.Show("已完成导入");
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message, "出错提示");
            }
        }
    }
}
